﻿using System;
using System.Data;
using System.Collections.Generic;
using ExplorerPlus.DomainObjects;

namespace ExplorerPlus.PersistenceLayer.Sql
{
    public static class DataAccess
    {
        public static Item GetItem(int categoryId, int itemId)
        {
            Item item = new Item(categoryId);

            using (IDataReader reader = Common.ExecuteDataReader(
                "SELECT * FROM Item WHERE (ItemId = " + itemId.ToString() 
                + ") AND (CategoryId = " + categoryId.ToString() + ")"))
            {
                while (reader.Read())
                {
                    item = new Item(Convert.ToInt32(reader["CategoryId"]), Convert.ToInt32(reader["ItemId"]));
                    item.ItemName = reader["ItemName"].ToString();
                    item.FilePath = reader["FilePath"].ToString();
                }

                reader.Close();
            }

            using (IDataReader reader = Common.ExecuteDataReader(
                "SELECT CategoryField.FieldId, ItemProperty.ItemPropertyId, ItemProperty.ItemPropertyValue, CategoryField.FieldName " 
                + "FROM ItemProperty INNER JOIN CategoryField ON ItemProperty.FieldId = CategoryField.FieldId AND ItemProperty.CategoryId = CategoryField.CategoryId "
                + "WHERE (ItemProperty.ItemId = " + itemId.ToString() + ")")) // OR (ItemProperty.ItemId IS NULL)
            {
                while (reader.Read())
                {
                    Item.ItemProperty itemProperty = new Item.ItemProperty();
                    itemProperty.FieldId = Convert.ToInt32(reader["FieldId"]);
                    itemProperty.FieldName = reader["FieldName"].ToString();
                    itemProperty.ItemPropertyId = Convert.ToInt32(reader["ItemPropertyId"]);
                    itemProperty.ItemPropertyValue = reader["ItemPropertyValue"].ToString();
                    item.ItemProperties.Add(itemProperty);
                }

                reader.Close();
            }

            return item;
        }

        public static void AddItem(Item item)
        {
            object value = Common.ExecuteScalar(
                "SELECT MAX(ItemId) + 1 FROM Item WHERE CategoryId = " + item.CategoryId);
            if (value == DBNull.Value)
            {
                value = 1;
            }

            Common.ExecuteNonQuery("INSERT INTO Item (ItemId, CategoryId, ItemName, FilePath) "
                + "VALUES (" + item.ItemId
                + ", 1, N'a', N'v')");
        }

        private static void UpdateItemProperties(Item item)
        {
            Common.ExecuteNonQuery("DELETE * FROM ItemProperty WHERE CategoryId = "
                + item.CategoryId.ToString() + " AND ItemId = " + item.ItemId.ToString());

            for (int i = 0; i < item.ItemProperties.Count; i++)
            {
                Item.ItemProperty property = item.ItemProperties[i];
                property.ItemPropertyId = i + 1;
                Common.ExecuteNonQuery("INSERT INTO ItemProperty (CategoryId, ItemId, FieldId, ItemPropertyId, ItemPropertyValue) "
                    + " VALUES (" + item.CategoryId
                    + ", " + item.ItemId
                    + ", " + property.FieldId
                    + ", " + property.ItemPropertyId
                    + ", '" + property.ItemPropertyValue + "')");
            }
        }
    }
}
